import lombok.var;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author CHENTAIWEN
 * @create 2022/9/14 10:17
 */
public class update {
    public static final String URL ="jdbc:mysql://localhost:3306/yingz?characterEncoding=utf8&useSSL=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&serverTimezone=Asia/Shanghai&useAffectedRows=true";
    public static final String USER ="root";
    public static final String PASSWORD ="root";

    public static void main(String[] args) throws Exception {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        //拼接查询审核进度
        String sql="\n" +
                "SELECT cq.quotation_id,erl.examine_status,ef.sort\n" +
                "FROM\n" +
                "\twk_examine_record_log erl\n" +
                "\tINNER JOIN wk_crm_quotation cq ON cq.examine_record_id = erl.record_id\n" +
                "\tINNER JOIN wk_examine_record er on erl.examine_id=er.examine_id\n" +
                "\tINNER JOIN wk_examine_flow ef on ef.flow_id=erl.flow_id\n" +
                "\tWHERE type is not null and ((erl.examine_status!=1 and ef.sort!=3) or (ef.sort=3))\n" +
                "\tGROUP BY cq.quotation_id,erl.examine_status,ef.sort";
        System.out.println(sql);
        PreparedStatement pst=conn.prepareStatement(sql);
        ResultSet rs = pst.executeQuery();
        List<CrmQuotation> list = new ArrayList();
        while (rs.next()) {
            CrmQuotation quotation = new CrmQuotation();
            Integer quotationId = rs.getInt("quotation_id");
            Integer sort = rs.getInt("sort");
            Integer examineStatus = rs.getInt("examine_status");
            quotation.setQuotationId(quotationId);
            quotation.setSort(sort);
            quotation.setExamineStatus(examineStatus);
            list.add(quotation);
        }
        System.out.println(list);
        System.out.println(list.size());

        for (CrmQuotation crmQuotation:list){
            String crmExmainEnum=null;
            Integer examineStatus= crmQuotation.getExamineStatus();
            Integer sort =crmQuotation.getSort();
            if (examineStatus==2){
                if (sort==1){
                    crmExmainEnum="已驳回至事业部专员";
                }else if(sort ==2){
                    crmExmainEnum="已驳回至事业部总经理审核";
                }else if(sort ==3){
                    crmExmainEnum="已驳回至市场部初审";
                }
//            crmExmainEnum="已驳回至事业部专员";
            }else if(examineStatus == 8){
                crmExmainEnum="作废";
            }else if (examineStatus ==1){
                crmExmainEnum="市场部已终审";
            }else if (examineStatus ==4){
                crmExmainEnum="待事业部专员审核";
            }else{
                if (sort==1){
                    crmExmainEnum="待事业部总经理审核";
                }else if(sort ==2){
                    crmExmainEnum="待市场部审核";
                }else if(sort ==3){
                    crmExmainEnum="市场部已初审";
                }
            }
            String updateSql= String.format("UPDATE wk_crm_quotation SET status=\"%s\" WHERE quotation_id = %d;", crmExmainEnum, crmQuotation.getQuotationId());
            System.out.println(updateSql);
            PreparedStatement updateQuotaion=conn.prepareStatement(updateSql);
            updateQuotaion.executeUpdate();
        }
        conn.close();

        pst.close();
    }
}
